package DAO;

import Util.ConnectionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Fornecedor;

public class FornecedorDAO {

    private Connection con;

    public FornecedorDAO() throws Exception {
        con = ConnectionUtil.getConn();
    }

    public void insert(Fornecedor item) throws SQLException {
        String SQL = "INSERT INTO FORNECEDOR VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement stmt = con.prepareStatement(SQL);

        stmt.setInt(1, item.getCodigo());
        stmt.setString(2, item.getNomeFantasia());
        stmt.setString(3, item.getRazaoSocial());
        stmt.setString(4, item.getPessoaContato());
        stmt.setString(5, item.getCnpj());
        stmt.setString(6, item.getInscMunicipal());
        stmt.setDate(7, new java.sql.Date(item.getDataCadastro().getTime()));
        stmt.setString(8, item.getEndereco());
        stmt.setInt(9, item.getNumero());
        stmt.setString(10, item.getComplemento());
        stmt.setString(11, item.getBairro());
        stmt.setString(12, item.getCidade());
        stmt.setString(13, item.getCep());
        stmt.setString(14, item.getUf());
        stmt.setString(15, item.getTelefone());
        stmt.setString(16, item.getTelefoneAlternativo());
        stmt.setString(17, item.getEmail());
        stmt.setString(18, item.getSite());
        stmt.setBoolean(19, item.isStatus()); 
        stmt.execute();

    }

    public void delete(int codigo) throws SQLException {
        String SQL = "DELETE FROM FORNECEDOR WHERE FRN_ID = ?";
        PreparedStatement stmt = con.prepareStatement(SQL);
        stmt.setInt(1, codigo);
        stmt.execute();
    }

    public void update(Fornecedor item) throws SQLException {
        String SQL = "UPDATE FORNECEDOR SET FRN_NOMEFANTASIA = ?, FRN_RAZAOSOCIAL = ?, FRN_PESSOACONTATO = ?, FRN_CNPJ = ?, FRN_INSCMUNICIPAL = ?, FRN_DATACADASTRO = ?, FRN_ENDERECO = ?, FRN_NUMERO = ?, FRN_COMPLEMENTO = ?, FRN_BAIRRO = ?, FRN_CIDADE = ?,FRN_CEP = ?, FRN_UF = ?, FRN_TELEFONE = ?, FRN_TELEFONEALTERNATIVO = ?, FRN_EMAIL = ?, FRN_SITE = ?, FRN_STATUS = ? WHERE FRN_ID = ?";
        PreparedStatement stmt = con.prepareStatement(SQL);
        
        stmt.setString(1, item.getNomeFantasia());
        stmt.setString(2, item.getRazaoSocial());
        stmt.setString(3, item.getPessoaContato());
        stmt.setString(4, item.getCnpj());
        stmt.setString(5, item.getInscMunicipal());
        stmt.setDate(6, new java.sql.Date(item.getDataCadastro().getTime()));
        stmt.setString(7, item.getEndereco());
        stmt.setInt(8, item.getNumero());
        stmt.setString(9, item.getComplemento());
        stmt.setString(10, item.getBairro());
        stmt.setString(11, item.getCidade());
        stmt.setString(12, item.getCep());
        stmt.setString(13, item.getUf());
        stmt.setString(14, item.getTelefone());
        stmt.setString(15, item.getTelefoneAlternativo());
        stmt.setString(16, item.getEmail());
        stmt.setString(17, item.getSite());
        stmt.setBoolean(18, item.isStatus()); 
        stmt.setInt(19, item.getCodigo());
        stmt.execute();
    }
    
    public Fornecedor findByCodigo(int codigo) throws Exception {
        String SQL = "SELECT FRN_ID, FRN_NOMEFANTASIA, FRN_RAZAOSOCIAL, FRN_PESSOACONTATO, FRN_CNPJ, FRN_INSCMUNICIPAL, FRN_DATACADASTRO, FRN_ENDERECO, FRN_NUMERO, FRN_COMPLEMENTO, FRN_BAIRRO, FRN_CIDADE, FRN_CEP, FRN_UF, FRN_TELEFONE, FRN_TELEFONEALTERNATIVO, FRN_EMAIL, FRN_SITE, FRN_STATUS FROM FORNECEDOR WHERE FRN_ID = ?";
        PreparedStatement p = con.prepareStatement(SQL);
        p.setInt(1, codigo);

        ResultSet rs = p.executeQuery();

        if (rs.next()) {
            Fornecedor forn = new Fornecedor();
            forn.setCodigo(rs.getInt("FRN_ID"));
            forn.setNomeFantasia(rs.getString("FRN_NOMEFANTASIA"));
            forn.setRazaoSocial(rs.getString("FRN_RAZAOSOCIAL"));
            forn.setPessoaContato(rs.getString("FRN_PESSOACONTATO"));
            forn.setCnpj(rs.getString("FRN_CNPJ"));
            forn.setInscMunicipal(rs.getString("FRN_INSCMUNICIPAL"));
            forn.setDataCadastro(rs.getDate("FRN_DATACADASTRO"));
            forn.setEndereco(rs.getString("FRN_ENDERECO"));
            forn.setNumero(rs.getInt("FRN_NUMERO"));
            forn.setComplemento(rs.getString("FRN_COMPLEMENTO"));
            forn.setBairro(rs.getString("FRN_BAIRRO"));
            forn.setCidade(rs.getString("FRN_CIDADE"));
            forn.setCep(rs.getString("FRN_CEP"));
            forn.setUf(rs.getString("FRN_UF"));
            forn.setTelefone(rs.getString("FRN_TELEFONE"));
            forn.setTelefoneAlternativo(rs.getString("FRN_TELEFONEALTERNATIVO"));
            forn.setEmail(rs.getString("FRN_EMAIL"));
            forn.setSite(rs.getString("FRN_SITE"));           
            forn.setStatus(rs.getBoolean("FRN_STATUS"));
            return forn;

        } else {
            return null;
        }
    }
    public List<Fornecedor> findByNome(String nome) throws Exception {
        String SQL = "SELECT FRN_ID, FRN_RAZAOSOCIAL, FRN_CNPJ, FRN_TELEFONE, FRN_EMAIL FROM FORNECEDOR WHERE FRN_RAZAOSOCIAL LIKE ?";
        PreparedStatement p = con.prepareStatement(SQL);
        p.setString(1, "%"+nome+"%");
        ResultSet rs = p.executeQuery();

        List<Fornecedor> lsFornecedor = new ArrayList<>();
        while (rs.next()) {
            Fornecedor forn = new Fornecedor();
            forn.setCodigo(rs.getInt("FRN_ID"));
            forn.setRazaoSocial(rs.getString("FRN_RAZAOSOCIAL"));
            forn.setCnpj(rs.getString("FRN_CNPJ"));
            forn.setTelefone(rs.getString("FRN_TELEFONE"));
            forn.setEmail(rs.getString("FRN_EMAIL"));
            
            lsFornecedor.add(forn);
        }
        return lsFornecedor;
    }
        public List<Fornecedor> findGrid() throws SQLException {

        String SQL = "SELECT FRN_ID, FRN_RAZAOSOCIAL, FRN_CNPJ, FRN_TELEFONE, FRN_EMAIL FROM FORNECEDOR WHERE 1=1";

        PreparedStatement p = con.prepareStatement(SQL);
        ResultSet rs = p.executeQuery();

        List<Fornecedor> lsFornecedor = new ArrayList<>();
        while (rs.next()) {
            Fornecedor forn = new Fornecedor();
            forn.setCodigo(rs.getInt("FRN_ID"));
            forn.setRazaoSocial(rs.getString("FRN_RAZAOSOCIAL"));
            forn.setCnpj(rs.getString("FRN_CNPJ"));
            forn.setTelefone(rs.getString("FRN_TELEFONE"));
            forn.setEmail(rs.getString("FRN_EMAIL"));
            
            lsFornecedor.add(forn);
        }
        return lsFornecedor;
    }
        public List<Fornecedor> findAll() throws SQLException {

        String SQL = "SELECT FRN_ID, FRN_NOMEFANTASIA, FRN_RAZAOSOCIAL, FRN_PESSOACONTATO, FRN_CNPJ, FRN_INSCMUNICIPAL, FRN_DATACADASTRO, FRN_ENDERECO, FRN_NUMERO, FRN_COMPLEMENTO, FRN_BAIRRO, FRN_CIDADE, FRN_CEP, FRN_UF, FRN_TELEFONE, FRN_TELEFONEALTERNATIVO, FRN_EMAIL, FRN_SITE, FRN_STATUS FROM FORNECEDOR WHERE 1=1";

        PreparedStatement p = con.prepareStatement(SQL);
        ResultSet rs = p.executeQuery();

        List<Fornecedor> lsFornecedor = new ArrayList<>();
        while (rs.next()) {
            Fornecedor forn = new Fornecedor();
            forn.setCodigo(rs.getInt("FRN_ID"));
            forn.setNomeFantasia(rs.getString("FRN_NOMEFANTASIA"));
            forn.setRazaoSocial(rs.getString("FRN_RAZAOSOCIAL"));
            forn.setPessoaContato(rs.getString("FRN_PESSOACONTATO"));
            forn.setCnpj(rs.getString("FRN_CNPJ"));
            forn.setInscMunicipal(rs.getString("FRN_INSCMUNICIPAL"));
            forn.setDataCadastro(rs.getDate("FRN_DATACADASTRO"));
            forn.setEndereco(rs.getString("FRN_ENDERECO"));
            forn.setNumero(rs.getInt("FRN_NUMERO"));
            forn.setComplemento(rs.getString("FRN_COMPLEMENTO"));
            forn.setBairro(rs.getString("FRN_BAIRRO"));
            forn.setCidade(rs.getString("FRN_CIDADE"));
            forn.setCep(rs.getString("FRN_CEP"));
            forn.setUf(rs.getString("FRN_UF"));
            forn.setTelefone(rs.getString("FRN_TELEFONE"));
            forn.setTelefoneAlternativo(rs.getString("FRN_TELEFONEALTERNATIVO"));
            forn.setEmail(rs.getString("FRN_EMAIL"));
            forn.setSite(rs.getString("FRN_SITE"));           
            forn.setStatus(rs.getBoolean("FRN_STATUS"));
            lsFornecedor.add(forn);
        }
        return lsFornecedor;
    }
}


